import htmlAnalysis.LabelAttribute;
import org.apache.commons.lang.math.NumberUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述: TODO Html解析导入Excel完整版
 *
 * @author yanzhengwu
 * @create 2020-12-25 13:56
 */
public class HtmlToExcel2 {


    public static void main(String[] args) throws IOException {
        String htmlStr = "<table border =\"1px solid #000000\">\n" +
                "        <tr height=\"50\">\n" +
                "            <td width=\"60\">左上角</td>\n" +
                "            <td colspan=\"3\" align=\"center\" bgcolor=\"red\">合并列1</td>\n" +
                "            <td colspan=\"3\" align=\"center\">合并列2</td>\n" +
                "        </tr>\n" +
                "        <tr>\n" +
                "            <td rowspan=\"4\">合并行</td>\n" +
                "            <td>表格1-1</td>\n" +
                "            <td colspan=\"2\" align=\"center\"><i><u><strong>表格1-2</strong></u></i></td>\n" +
                "            <td><s>表格1-3</s></td>\n" +
                "            <td>表格1-4</td>\n" +
                "            <td>表格1-5</td>\n" +
                "        </tr>\n" +
                "        <tr>\n" +
                "            <td>表格2-1</td>\n" +
                "            <td>表格2-2</td>\n" +
                "            <td>表格2-3</td>\n" +
                "            <td>表格2-4</td>\n" +
                "            <td>表格2-5</td>\n" +
                "            <td>表格2-6</td>\n" +
                "        </tr>\n" +
                "        <tr>\n" +
                "            <td>表格3-1</td>\n" +
                "            <td>表格3-2</td>\n" +
                "            <td>表格3-3</td>\n" +
                "            <td>表格3-4</td>\n" +
                "            <td>表格3-5</td>\n" +
                "            <td>表格3-6</td>\n" +
                "        </tr>\n" +
                "        <tr>\n" +
                "            <td>表格4-1</td>\n" +
                "            <td>表格4-2</td>\n" +
                "            <td>表格4-3</td>\n" +
                "            <td>表格4-4</td>\n" +
                "            <td>表格4-5</td>\n" +
                "            <td>表格4-6</td>\n" +
                "        </tr>\n" +
                "        <tr height=\"80\">\n" +
                "            <td colspan=\"7\" align=\"center\">\n" +
                "                <font color=\"#ff7f50\">\n" +
                "                    <strong>合并最后</strong>\n" +
                "                </font>\n" +
                "            </td>\n" +
                "        </tr>\n" +
                "    </table>";
        HtmlToExcel2 toWord = new HtmlToExcel2();
        Document doc = Jsoup.parse(htmlStr);
        Workbook wb = toWord.table2Excel(doc);
        OutputStream outputStream = new FileOutputStream("F:/text.xlsx");
        System.out.println("导出完成。");
        wb.write(outputStream);
        outputStream.close();

    }

    /**
     * html表格转excel
     *
     * @param doc
     * @return
     */
    public Workbook table2Excel( Document doc) {
        List<CrossRangeCellMeta> crossRowEleMetaLs = new ArrayList<>();
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();

        int rowIndex = 1;//开始行(从第二行开始生成表格)
        // 生成表体
        Elements tbody = doc.select("tbody");
        if (tbody != null) {
            List<Element> trLs = tbody.select("tr");
            for (Element trEle : trLs) {
                Row row = sheet.createRow(rowIndex);
                String height = trEle.attr("height");
                if (null != height && !"".equals(height)) {
                    row.setHeight((short) (Short.parseShort(height)*15));

                }
                List<Element> tdLs = trEle.select("td");
                makeRowCell(wb, tdLs, rowIndex, row, 1, crossRowEleMetaLs);
                rowIndex++;
            }
        }
        //合并单元格需要重新写样式
        for (CrossRangeCellMeta crcm : crossRowEleMetaLs) {
            sheet.addMergedRegion(new CellRangeAddress(crcm.getFirstRow(), crcm.getLastRow(), crcm.getFirstCol(), crcm.getLastCol()));
            System.out.println("开始行"+crcm.getFirstRow()+"--结束行"+crcm.getLastRow()+"--开始列"+crcm.getFirstCol()+"--结束列"+crcm.getLastCol());
            for (int i =crcm.getFirstRow(); i <= crcm.getLastRow(); i++) {
                for (int j = crcm.getFirstCol(); j <= crcm.getLastCol(); j++) {
                    sheet.getRow(i).getCell(j).setCellStyle(crcm.getCellStyle());
                }
            }
        }

        return wb;
    }

    /**
     * 生产行内容
     *
     * @return 最后一列的cell index
     */
    /**
     * @param tdLs              th或者td集合
     * @param rowIndex          行号
     * @param row               POI行对象
     * @param startCellIndex    开始行下标
     * @param crossRowEleMetaLs 跨行元数据集合
     * @return
     */
    private int makeRowCell(Workbook wb, List<Element> tdLs, int rowIndex, Row row, int startCellIndex,
                            List<CrossRangeCellMeta> crossRowEleMetaLs) {
        //开始列数（如果从第二列）
        int i = 1;
        for (int eleIndex = 0; eleIndex < tdLs.size(); i++, eleIndex++) {
            //记录合并单元格坐标
            int captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
            while (captureCellSize > 0) {
                for (int j = 0; j < captureCellSize; j++) {// 当前行跨列处理（补单元格）
                    row.createCell(i);
                    i++;
                }
                captureCellSize = getCaptureCellSize(rowIndex, i, crossRowEleMetaLs);
            }
            Element tdEle = tdLs.get(eleIndex);
            String val = tdEle.text();

            Cell c = row.createCell(i);
            if (NumberUtils.isNumber(val)) {
                c.setCellValue(Double.parseDouble(val));
                c.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
            } else {
                c.setCellValue(val);
            }
            //获取前端样式写入单元格，并记录合并单元格样式
            LabelAttribute attribute = new LabelAttribute(wb);
            attribute.getHtmlAttribute(tdEle);
            CellStyle cellStyle = attribute.makeCellStyle();
            c.setCellStyle(cellStyle);

            int rowSpan = NumberUtils.toInt(tdEle.attr("rowspan"), 1);
            int colSpan = NumberUtils.toInt(tdEle.attr("colspan"), 1);
            if (rowSpan > 1 || colSpan > 1) { // 存在跨行或跨列
                crossRowEleMetaLs.add(new CrossRangeCellMeta(rowIndex, i, rowSpan, colSpan, cellStyle));
            }
            if (colSpan > 1) {// 当前行跨列处理（补单元格）
                for (int j = 1; j < colSpan; j++) {
                    i++;
                    row.createCell(i);
                }
            }
        }
        return i;
    }

    /**
     * 获得因rowSpan占据的单元格
     *
     * @param rowIndex          行号
     * @param colIndex          列号
     * @param crossRowEleMetaLs 跨行列元数据
     * @return 当前行在某列需要占据单元格
     */
    private static int getCaptureCellSize(int rowIndex, int colIndex, List<CrossRangeCellMeta> crossRowEleMetaLs) {
        int captureCellSize = 0;
        for (CrossRangeCellMeta crossRangeCellMeta : crossRowEleMetaLs) {
            if (crossRangeCellMeta.getFirstRow() < rowIndex && crossRangeCellMeta.getLastRow() >= rowIndex) {
                if (crossRangeCellMeta.getFirstCol() <= colIndex && crossRangeCellMeta.getLastCol() >= colIndex) {
                    captureCellSize = crossRangeCellMeta.getLastCol() - colIndex + 1;
                }
            }
        }
        return captureCellSize;
    }

    /**
     * 获得标题样式
     *
     * @param workbook
     * @return
     */
    private static CellStyle getTitleStyle(Workbook workbook) {
        short titlebackgroundcolor = HSSFColor.GREY_25_PERCENT.index;
        java.awt.Color color1 = java.awt.Color.decode("#ff0000");
        short c = (short) color1.getRGB();
        //Color color2 = new XSSFColor(color1);
        //short titlebackgroundcolor = ((XSSFColor) color2).getIndex();

        short fontSize = 12;
        String fontName = "宋体";
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(titlebackgroundcolor);// 背景色


        style.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderBottom(XSSFCellStyle.BORDER_THIN);//下边框
        style.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER);//设置水平居中
        style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);//设置垂直居中
        Font font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints(fontSize);
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setColor(Font.COLOR_RED);

        style.setFont(font);

        return style;
    }

    /**
     * 获得内容样式
     *
     * @param wb
     * @return
     */
    private static CellStyle getContentStyle(Workbook wb) {
        short fontSize = 12;
        String fontName = "宋体";
        CellStyle style = wb.createCellStyle();
        style.setBorderBottom((short) 1);
        style.setBorderTop((short) 1);
        style.setBorderLeft((short) 1);
        style.setBorderRight((short) 1);

        Font font = wb.createFont();
        //font.setColor((short) 2);//字体颜色
        font.setFontName(fontName);//字体名称
        font.setFontHeightInPoints(fontSize);//字体大小
        font.setItalic(true);//斜体
        font.setBold(true);//加粗
        font.setUnderline((byte) 1);//下划线
        font.setStrikeout(true);//删除线

        style.setFont(font);
        return style;
    }


    class CrossRangeCellMeta {

        private CellStyle cellStyle;//单元格样式
        private int firstRowIndex;//开始行
        private int firstColIndex;//开始列
        private int rowSpan;// 跨越行数
        private int colSpan;// 跨越列数


        public CrossRangeCellMeta(int firstRowIndex, int firstColIndex, int rowSpan, int colSpan, CellStyle cellStyle) {
            super();
            this.cellStyle = cellStyle;
            this.firstRowIndex = firstRowIndex;
            this.firstColIndex = firstColIndex;
            this.rowSpan = rowSpan;
            this.colSpan = colSpan;
        }

        /**
         * 开始行
         *
         * @return
         */
        int getFirstRow() {
            return firstRowIndex;
        }

        /**
         * 结束行
         *
         * @return
         */
        int getLastRow() {
            return firstRowIndex + rowSpan - 1;
        }

        /**
         * 开始列
         *
         * @return
         */
        int getFirstCol() {
            return firstColIndex;
        }

        /**
         * 结束列
         *
         * @return
         */
        int getLastCol() {
            return firstColIndex + colSpan - 1;
        }

        /**
         * 获取样式
         *
         * @return
         */
        CellStyle getCellStyle() {
            return cellStyle;
        }

    }
}
